package com.gem.controller;

import com.alibaba.druid.util.StringUtils;
import com.gem.domain.*;
import com.gem.service.CampusConnService;
import com.gem.service.CampusService;
import com.gem.service.CoreService;
import com.gem.service.UserService;
import com.gem.util.ExcelUtil;
import com.gem.util.MessageUtil;
import com.gem.util.PageUtil;
import com.github.pagehelper.PageInfo;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.OutputStream;
import java.util.Date;
import java.util.List;

import static com.gem.util.ExcelUtil.*;

/**
 * @Author: 玲
 * @Description: 我的院校控制器
 * @create 2019-10-18 14:11
 * @Modified By:
 */
@Controller
@SuppressWarnings("all")
public class CampusController {
    @Autowired
    private CampusService service;

    @Autowired
    private CampusConnService connService;

    @Autowired
    private CoreService coreService;

    @Autowired
    private UserService userService;

    @RequestMapping("/campus/infoFindAll")
    public String findAll(Model model,HttpSession session,
                          @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                          @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {
        try {
            User user = (User) session.getAttribute("user");
            List<User> users = userService.findMmembers();
            model.addAttribute("users", users);
            Campus campus = new Campus();
            campus.setPersonname(user.getPersonname());
            PageInfo<Campus> pageBeans = null;
            if (user.getAuthority() == 1){
                pageBeans = service.findAll(currentPage, pageSize);
            }else {
                pageBeans = service.search(campus, currentPage, pageSize);
            }
            if (pageBeans != null && pageBeans.getList().size() > 0) {
                System.out.println(pageBeans.getList());
                model.addAttribute("pageBean", pageBeans);
                List<Integer> pageValue = PageUtil.getPageValue(pageBeans.getPages(), currentPage);
                model.addAttribute("pageValue", pageValue);
            } else {
                model.addAttribute("info", "没有数据请先添加！");
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/", "服务器异常！请联系管理员！", model);
            return "message";
        }
        return "campus/info";
    }

    @RequestMapping("/campus/saveUI")
    public String saveUI(Model model) {
        try {
            List<User> users = userService.findMmembers();
            model.addAttribute("users", users);
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/campus/infoFindAll", "服务器异常！请联系管理员！", model);
            return "message";
        }
        return "campus/info_insert";
    }

    @RequestMapping("/campus/save")
    public String save(Campus campus, CampusInfo info, Model model) {
        try {
            service.save(campus, info);
            MessageUtil.returnInfoPage("3", "/campus/infoFindAll", "添加成功！", model);
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/campus/infoFindAll", "服务器异常！请联系管理员！", model);
        }
        return "message";
    }

    @RequestMapping("/campus/search")
    public String search(Model model, Campus campus,
                         @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                         @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {
        try {
            String campusName = campus.getCampusName();
            String personname = campus.getPersonname();
            PageInfo<Campus> pageBeans = service.search(campus, currentPage, pageSize);
            if (pageBeans.getList() != null && pageBeans.getList().size() > 0) {
                model.addAttribute("pageBean", pageBeans);
                List<Integer> pageValue = PageUtil.getPageValue(pageBeans.getPages(), currentPage);
                model.addAttribute("pageValue", pageValue);
                model.addAttribute("searchUrl", "/campus/search");
                model.addAttribute("campusName", campusName);
                model.addAttribute("personname", personname);
            } else {
                model.addAttribute("info", "没有数据请先添加！");
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/", "服务器异常！请联系管理员！", model);
            return "message";
        }
        return "campus/info";
    }


    @RequestMapping("/campus/deleteById")
    @ResponseBody
    public String deleteById(Integer campusId,int currentPageSize, int currentPage, int lastPage){
        try {
            if (service.deleteById(campusId)>0){
                if (currentPageSize == 1 && currentPage == lastPage) {
                    return "0";
                }
            }
            return "1";
        } catch (Exception e) {
            e.printStackTrace();
            return "-1";
        }
    }

    @RequestMapping("/campus/deleteAll")
    public String deleteAll(int[] campusIds, Model model) {
        try {
            if (campusIds != null && campusIds.length != 0) {
                service.deleteAll(campusIds);
                MessageUtil.returnInfoPage("3", "/campus/infoFindAll", "删除成功！", model);
            } else {
                MessageUtil.returnInfoPage("3", "/campus/infoFindAll", "没有传递参数！", model);
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/campus/infoFindAll", "服务器异常！请联系管理员!", model);
        }
        return "message";
    }

    @RequestMapping("/campus/updateById")
    public String updaetUI(Integer campusId, Model model,
                           @RequestParam(value = "currentPage") int currentPage,
                           @RequestParam(value = "pageSize") int pageSize) {
        model.addAttribute("currentPage", currentPage);
        model.addAttribute("pageSize", pageSize);
        try {
            List<User> users = userService.findMmembers();
            model.addAttribute("users", users);
            Campus campus = service.findById(campusId);
            model.addAttribute("campus", campus);
            User one = userService.findById(campus.getMarketEmployeeId());
            model.addAttribute("one", one);
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/campus/infoFindAll", "服务器异常！请联系管理员！", model);
            return "message";
        }
        return "campus/info_update";
    }

    @RequestMapping("/campus/update")
    public String update(Campus campus, Model model,
                         @RequestParam(value = "currentPage", defaultValue = "1") int currentPage,
                         @RequestParam(value = "pageSize", defaultValue = "5") int pageSize) {
        try {
            if (service.update(campus) == 1) {
                MessageUtil.returnInfoPage("3", "/campus/infoFindAll?currentPage=" + currentPage +
                        "&pageSize=" + pageSize, "修改成功！", model);
            } else {
                MessageUtil.returnInfoPage("3", "/campus/infoFindAll?currentPage=" + currentPage +
                        "&pageSize=" + pageSize, "未作修改！", model);
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/campus/infoFindAll", "修改失败！请联系管理员！", model);
        }
        return "message";
    }

    @RequestMapping("/campus/campusInfo")
    public String campusInfo(Integer campusId, Model model,
                             @RequestParam("RcurrentPage") int RcurrentPage,
                             @RequestParam("RpageSize") int RpageSize) {
        model.addAttribute("RcurrentPage", RcurrentPage);
        model.addAttribute("RpageSize", RpageSize);
        try {
            CampusInfo info = service.findInfo(campusId);
            model.addAttribute("CampusInfo", info);
            model.addAttribute("campusId", campusId);
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/campus/infoFindAll", "服务器异常！请联系管理员！", model);
            return "message";
        }
        return "campus/info_university";
    }

    @RequestMapping("/campus/infoUpdateUI")
    public String infoUpdateUI(Integer campusId, Model model,
                               @RequestParam("RcurrentPage") int RcurrentPage,
                               @RequestParam("RpageSize") int RpageSize) {
        model.addAttribute("RcurrentPage", RcurrentPage);
        model.addAttribute("RpageSize", RpageSize);
        try {
            CampusInfo info = service.findInfo(campusId);
            model.addAttribute("CampusInfo", info);
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/campus/infoFindAll", "服务器异常！请联系管理员！", model);
            return "message";
        }
        return "campus/info_university_update";
    }

    @RequestMapping("/campus/infoUpdate")
    public String infoUpdate(CampusInfo info, Model model,
                             @RequestParam("RcurrentPage") int RcurrentPage,
                             @RequestParam("RpageSize") int RpageSize) {
        model.addAttribute("RcurrentPage", RcurrentPage);
        model.addAttribute("RpageSize", RpageSize);
        try {
            if (service.updateInfo(info) == 1) {
                MessageUtil.returnInfoPage("3", "/campus/campusInfo?campusId=" + info.getCampusId()
                        + "&RcurrentPage=" + RcurrentPage + "&RpageSize=" + RpageSize, "修改成功！", model);
            } else {
                MessageUtil.returnInfoPage("3", "/campus/campusInfo?campusId=" + info.getCampusId()
                        + "&RcurrentPage=" + RcurrentPage + "&RpageSize=" + RpageSize, "未作修改！", model);
            }
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/campus/campusInfo?campusId=" + info.getCampusId()
                    + "&RcurrentPage=" + RcurrentPage + "&RpageSize=" + RpageSize, "服务器异常！请联系管理员！", model);

        }
        return "message";
    }


    @RequestMapping("/campus/infoSaveUI")
    public String infoSaveUI(Integer campusId, Model model,
                             @RequestParam("RcurrentPage") int RcurrentPage,
                             @RequestParam("RpageSize") int RpageSize) {
        model.addAttribute("RcurrentPage", RcurrentPage);
        model.addAttribute("RpageSize", RpageSize);
        try {
            Campus campus = service.findById(campusId);
            model.addAttribute("campus", campus);
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/campus/infoFindAll", "服务器异常！请联系管理员！", model);
            return "message";
        }
        return "campus/info_university_insert";
    }

    @RequestMapping("/campus/infoSave")
    public String infoSave(CampusInfo info, Model model,
                           @RequestParam("RcurrentPage") int RcurrentPage,
                           @RequestParam("RpageSize") int RpageSize) {
        try {
            service.saveInfo(info);
            MessageUtil.returnInfoPage("3", "/campus/campusInfo?campusId="
                    + info.getCampusId() + "&RcurrentPage=" + RcurrentPage + "&RpageSize=" + RpageSize, "保存成功！", model);
        } catch (Exception e) {
            e.printStackTrace();
            MessageUtil.returnInfoPage("3", "/campus/infoFindAll", "服务器异常！请联系管理员！", model);

        }
        return "message";
    }

    @RequestMapping("/campus/infoDelete")
    @ResponseBody
    public String infoDelete(Integer campusInfoId){
        try {
            service.deleteInfo(campusInfoId);
            return "1";
        } catch (Exception e) {
            e.printStackTrace();
            return "-1";
        }
    }

    //导出院校excel模板
    @RequestMapping("/campus/exportTemplate")
    @Transactional(rollbackFor=Exception.class)
    public void exportExcelTemplate(HttpServletResponse response, HttpSession session) throws Exception {
       try {
           //创建excel工作簿对象
           HSSFWorkbook workbook = new HSSFWorkbook();

           //创建水平垂直居中的cellStyle单元格样式对象
           HSSFCellStyle cellStyle = ExcelUtil.getTitleStyle(workbook);

           //创建水平垂直居中加粗的cellStyle单元格样式,给表头试用
           HSSFCellStyle cellStyle2 = ExcelUtil.getThStyle(workbook);

           //模板第一行例子展示
           HSSFCellStyle rowStyle = ExcelUtil.getExamplStyle(workbook);

           // 单元格类型日期格式化
           HSSFCellStyle cellStyleDate = ExcelUtil.getDateRedStyle(workbook);

           //创建行对象
           HSSFRow row = null;

           //(--我的院校表--)
           //创建sheet对象
           HSSFSheet sheet = workbook.createSheet("我的院校");

           // 将第一行的三个单元格给合并,模板固定
           //注意 java的参数是从0开始 即表示excel中的一行或者第一列
           sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
           row = sheet.createRow(0);
           HSSFCell beginCell0 = row.createCell(0);
           beginCell0.setCellValue("我的院校表");
           //cellStyle为水平居中字体加粗单元格样式
           beginCell0.setCellStyle(cellStyle);

           //第二行的单元格进行合并,语句提示
           sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 12));
           row = sheet.createRow(1);
           HSSFCell beginCell1 = row.createCell(0);
           beginCell1.setCellValue("请按照模板字段和单元格格式提交数据,日期类型请使用给定格式刷刷一下");
           //cellStyle为水平居中字体加粗单元格样式
           beginCell1.setCellStyle(cellStyle);

           //次数写表头,根据各个页面的进行修改
           HSSFRow headRow = sheet.createRow(2);
           headRow.createCell(0).setCellValue("院校名称");
           headRow.createCell(1).setCellValue("院校专业");
           headRow.createCell(2).setCellValue("渠道专员");
           headRow.createCell(3).setCellValue("创建时间");
           headRow.createCell(4).setCellValue("修改时间");
           headRow.createCell(5).setCellValue("备注");
           headRow.setHeightInPoints(25); // 设置行的高度
           for (int i = 0; i < 6; i++) {
               headRow.getCell(i).setCellStyle(cellStyle2);
           }

           //下拉列表的参数--渠道专员（市场）
           User user = (User) session.getAttribute("user");
           String[] personName = {user.getPersonname()};

           //设置当前工作表的设么地方需要下拉,需要自己对着表格设置位置,没有就不需要写
           sheet = setHSSFValidation(sheet, personName, 3, 10000, 2, 2);

           //优化 降低冗余
           HSSFCell cell = null;

           //设置所有列列宽
           for (int i = 0; i < 6; i++) {
               sheet.setColumnWidth(i, 20 * 256);
           } // 设置列的宽度

           //在第四行给一行数据模板,根据对应表的对应字段填写

           row = sheet.createRow(3);

           //第一列,名字
           cell = row.createCell(0);
           cell.setCellStyle(rowStyle);
           cell.setCellValue("院校名称");
           cell = row.createCell(1);
           cell.setCellStyle(rowStyle);
           cell.setCellValue("院校专业");
           cell = row.createCell(2);
           cell.setCellStyle(rowStyle);
           cell.setCellValue(user.getPersonname());

           cell = row.createCell(3);
           cell.setCellStyle(cellStyleDate);//改单元格为日期样式
           cell.setCellValue(new Date());//赋值

           cell = row.createCell(4);
           cell.setCellStyle(cellStyleDate);//改单元格为日期样式
           cell.setCellValue(new Date());//赋值

           cell = row.createCell(5);
           cell.setCellStyle(rowStyle);
           cell.setCellValue("没啥备注");

           OutputStream outputStream = response.getOutputStream();
           response.reset();
           response.setContentType("application/vnd.ms-excel");
           response.setHeader("Content-disposition", "attachment;filename=template.xls");

           workbook.write(outputStream);
           outputStream.flush();
           outputStream.close();
       }catch (Exception e){
           e.printStackTrace();
           TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
       }
    }

    //导出院校excel
    @RequestMapping("/campus/export")
    @Transactional(rollbackFor=Exception.class)
    public void exportExcel(HttpServletResponse response) throws Exception {
        try {
            //创建excel工作簿对象
            HSSFWorkbook workbook = new HSSFWorkbook();

            //创建水平垂直居中的cellStyle单元格样式对象
            HSSFCellStyle cellStyle = ExcelUtil.getTitleStyle(workbook);

            //创建水平垂直居中加粗的cellStyle单元格样式,给表头试用
            HSSFCellStyle cellStyle2 = ExcelUtil.getThStyle(workbook);

            // 单元格类型日期格式化
            HSSFCellStyle cellStyleDate = ExcelUtil.getDateStyle(workbook);
            //创建行对象
            HSSFRow row = null;

            //我的院校
            //创建sheet对象
            HSSFSheet sheet = workbook.createSheet("我的院校");

            // 将第一行的三个单元格给合并,模板固定
            //注意 java的参数是从0开始 即表示excel中的一行或者第一列
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
            row = sheet.createRow(0);
            HSSFCell beginCell0 = row.createCell(0);
            beginCell0.setCellValue("我的院校表");
            //cellStyle为水平居中字体加粗单元格样式
            beginCell0.setCellStyle(cellStyle);

            HSSFRow headRow = sheet.createRow(2);
            headRow.createCell(0).setCellValue("院校名称");
            headRow.createCell(1).setCellValue("院校专业");
            headRow.createCell(2).setCellValue("渠道专员");
            headRow.createCell(3).setCellValue("创建时间");
            headRow.createCell(4).setCellValue("修改时间");
            headRow.createCell(5).setCellValue("备注");
            headRow.setHeightInPoints(25); // 设置行的高度
            for (int i = 0; i < 6; i++) {
                headRow.getCell(i).setCellStyle(cellStyle2);
            }

            //创建水平垂直居中加粗的cellStyle单元格样式,给数据试用
            HSSFCellStyle rowStyle = ExcelUtil.getCenterStyle(workbook);

            //调用service方法查询所有数据集合
            List<Campus> campusList = service.findAllCampus();

            //优化 降低冗余
            HSSFCell cell = null;
            Campus campus = null;
            //设置所有列列宽
            for (int i = 0; i < 6; i++) {
                sheet.setColumnWidth(i, 20 * 256);
            } // 设置列的宽度
            for (int i = 0; i < campusList.size(); i++) {
                row = sheet.createRow(i + 3);
                campus = campusList.get(i);
                //第一列 院校名称
                cell = row.createCell(0);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(campus.getCampusName());

                //第二列 院系专业
                cell = row.createCell(1);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(campus.getCampusMajor());

                //第三列 渠道专员
                cell = row.createCell(2);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(campus.getPersonname());

                //第四列 创建时间
                cell = row.createCell(3);
                cell.setCellStyle(cellStyleDate);//改单元格为日期样式
                cell.setCellValue(campus.getCampusCreateDate());//赋值
                //第五列 修改时间
                cell = row.createCell(4);
                cell.setCellStyle(cellStyleDate);//改单元格为日期样式
                cell.setCellValue(campus.getCampusModifyDate());//赋值

                //第六列 渠道专员
                cell = row.createCell(5);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(campus.getCampusRemarks());
            }

            //院校详情
            //创建sheet对象
            HSSFSheet sheet1 = workbook.createSheet("院校详情");
            sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
            row = sheet1.createRow(0);
            HSSFCell beginCell_10 = row.createCell(0);
            beginCell_10.setCellValue("院校详情表");
            //cellStyle为水平居中字体加粗单元格样式
            beginCell_10.setCellStyle(cellStyle);

            /*次数写表头,根据各个页面的进行修改*/
            HSSFRow headRow1 = sheet1.createRow(2);
            headRow1.createCell(0).setCellValue("院校名称");
            headRow1.createCell(1).setCellValue("专业情况");
            headRow1.createCell(2).setCellValue("教学安排");
            headRow1.createCell(3).setCellValue("学制情况");
            headRow1.createCell(4).setCellValue("实训氛围");
            headRow1.createCell(5).setCellValue("竞争对手");
            headRow1.createCell(6).setCellValue("招生量(近两年)");
            headRow1.createCell(7).setCellValue("备注");
            headRow1.setHeightInPoints(25); // 设置行的高度
            for (int i = 0; i < 8; i++) {
                headRow1.getCell(i).setCellStyle(cellStyle2);
            }
            //设置所有列列宽
            for (int i = 0; i < 8; i++) {
                sheet1.setColumnWidth(i, 20 * 300);
            } // 设置列的宽度

            List<CampusInfo> infoList = service.findAllInfo();
            CampusInfo info = null;

            for (int i = 0; i < infoList.size(); i++) {
                row = sheet1.createRow(i + 3);
                info = infoList.get(i);

                cell = row.createCell(0);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(info.getCampusName());

                cell = row.createCell(1);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(info.getCampusInfoProfessionSituation());

                cell = row.createCell(2);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(info.getCampusInfoLayout());

                cell = row.createCell(3);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(info.getCampusInfoEducationSituation());

                cell = row.createCell(4);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(info.getCampusInfoAtmosphere());

                cell = row.createCell(5);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(info.getCampusInfoCompetitor());

                cell = row.createCell(6);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(info.getCampusInfoStuCount());

                cell = row.createCell(7);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(info.getCampusInfoRemarks());

            }
            //院校联系人
            //创建sheet对象
            HSSFSheet sheet2 = workbook.createSheet("院校联系人");
            sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
            row = sheet2.createRow(0);
            HSSFCell beginCell_20 = row.createCell(0);
            beginCell_20.setCellValue("院校联系人表");
            //cellStyle为水平居中字体加粗单元格样式
            beginCell_20.setCellStyle(cellStyle);

            /*次数写表头,根据各个页面的进行修改*/
            HSSFRow headRow2 = sheet2.createRow(2);
            headRow2.createCell(0).setCellValue("姓名");
            headRow2.createCell(1).setCellValue("性别");
            headRow2.createCell(2).setCellValue("职务");
            headRow2.createCell(3).setCellValue("渠道专员");
            headRow2.createCell(4).setCellValue("联系方式");
            headRow2.createCell(5).setCellValue("个人情况");
            headRow2.createCell(6).setCellValue("备注");
            headRow2.createCell(7).setCellValue("创建时间");
            headRow2.createCell(8).setCellValue("修改时间");
            headRow2.setHeightInPoints(25); // 设置行的高度
            for (int i = 0; i < 9; i++) {
                headRow2.getCell(i).setCellStyle(cellStyle2);
            }
            //设置所有列列宽
            for (int i = 0; i < 9; i++) {
                sheet2.setColumnWidth(i, 25 * 256);
            } // 设置列的宽度

            List<CampusConn> campusConnList = connService.findAllConn();
            CampusConn campusConn = null;

            for (int i = 0; i < campusConnList.size(); i++) {
                row = sheet2.createRow(i + 3);
                campusConn = campusConnList.get(i);
                //第一列 姓名
                cell = row.createCell(0);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(campusConn.getCampusConnName());

                //第二列 性别
                cell = row.createCell(1);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(campusConn.getCampusConnSex());

                //第三列 职务
                cell = row.createCell(2);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(campusConn.getCampusConnPosition());
                //第四列 渠道专员
                cell = row.createCell(3);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(userService.findById(campusConn.getMarketEmployeeId()).getPersonname());

                //第五列 联系方式
                cell = row.createCell(4);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(campusConn.getCampusConnWay());

                //第六列 职务
                cell = row.createCell(5);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(campusConn.getCampusConnPosition());

                //第七列 备注
                cell = row.createCell(6);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(campusConn.getCampusConnRemarks());

                //第八列 创建时间
                cell = row.createCell(7);
                cell.setCellStyle(cellStyleDate);//改单元格为日期样式
                cell.setCellValue(campusConn.getCampusConnCreateDate());//赋值

                //第九列 修改时间
                cell = row.createCell(8);
                cell.setCellStyle(cellStyleDate);//改单元格为日期样式
                cell.setCellValue(campusConn.getCampusConnModifyDate());//赋值
            }

            //核心事件
            //创建sheet对象
            HSSFSheet sheet3 = workbook.createSheet("核心事件");
            sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));
            row = sheet3.createRow(0);
            HSSFCell beginCell_30 = row.createCell(0);
            beginCell_30.setCellValue("核心事件表");
            //cellStyle为水平居中字体加粗单元格样式
            beginCell_30.setCellStyle(cellStyle);

            /*次数写表头,根据各个页面的进行修改*/
            HSSFRow headRow3 = sheet3.createRow(2);
            headRow3.createCell(0).setCellValue("事件名称");
            headRow3.createCell(1).setCellValue("开始时间");
            headRow3.createCell(2).setCellValue("规划达成时间");
            headRow3.createCell(3).setCellValue("实际达成时间");
            headRow3.createCell(4).setCellValue("所属院校");
            headRow3.createCell(5).setCellValue("备注");
            headRow3.setHeightInPoints(25); // 设置行的高度
            for (int i = 0; i < 6; i++) {
                headRow3.getCell(i).setCellStyle(cellStyle2);
            }
            //设置所有列列宽
            for (int i = 0; i < 6; i++) {
                sheet3.setColumnWidth(i, 25 * 256);
            } // 设置列的宽度

            List<CoreEvent> coreEventList = coreService.findAll();
            CoreEvent coreEvent = null;
            for (int i = 0; i < coreEventList.size(); i++) {
                row = sheet3.createRow(i + 3);
                coreEvent = coreEventList.get(i);
                //第一列 事件名称
                cell = row.createCell(0);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(coreEvent.getCoreEventName());

                //第二列 开始时间
                cell = row.createCell(1);
                cell.setCellStyle(cellStyleDate);//改单元格为日期样式
                cell.setCellValue(coreEvent.getBeginTime());//赋值

                //第三列 规划达成时间
                cell = row.createCell(2);
                cell.setCellStyle(cellStyleDate);//改单元格为日期样式
                cell.setCellValue(coreEvent.getPlanTime());//赋值

                //第四列 实际达成时间
                cell = row.createCell(3);
                cell.setCellStyle(cellStyleDate);//改单元格为日期样式
                cell.setCellValue(coreEvent.getCompleteTime());//赋值

                //第五列 所属院校
                cell = row.createCell(4);
                cell.setCellStyle(rowStyle);
                System.out.println(coreEvent.getCampusId());
                Campus byId = service.findById(coreEvent.getCampusId());

                cell.setCellValue(byId.getCampusName());

                //第六列 备注
                cell = row.createCell(5);
                cell.setCellStyle(rowStyle);
                cell.setCellValue(coreEvent.getCoreRemarks());
            }

            OutputStream outputStream = response.getOutputStream();
            response.reset();
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=MyCampus.xls");

            workbook.write(outputStream);
            outputStream.flush();
            outputStream.close();
        }catch (Exception e){
            e.printStackTrace();
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
        }
    }

    //导入院校excel
    @RequestMapping("/campus/import")
    @ResponseBody
    @Transactional(rollbackFor=Exception.class)
    public String importExcel(@RequestParam("file") MultipartFile file, Model model) {
        try {
            //固定格式  参数file 为 文件上传的input的name
            HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
            HSSFSheet sheet = workbook.getSheetAt(0);

            int lastRowNum = sheet.getLastRowNum();

            Campus campus = null;
            HSSFRow row = null;

            //从第四行开始读取数据
            for (int i = 3; i <= lastRowNum; i++) {
                campus = new Campus();
                row = sheet.getRow(i);

                if (StringUtils.isEmpty(row.getCell(0).getStringCellValue()) && StringUtils.isEmpty(row.getCell(1).getStringCellValue())) {
                    break;//如果是空行无数据，停止写入
                }
                campus.setCampusName(row.getCell(0).getStringCellValue());

                campus.setCampusMajor(row.getCell(1).getStringCellValue());

                //查询渠道专员的uid
                User user = userService.findByName(row.getCell(2).getStringCellValue());
                campus.setMarketEmployeeId(user.getUid());

                Date dsDate = row.getCell(3).getDateCellValue();
                campus.setCampusCreateDate(dsDate);

                Date dDate = row.getCell(4).getDateCellValue();
                campus.setCampusModifyDate(dDate);

                campus.setCampusRemarks(row.getCell(5).getStringCellValue());

                //添加一个对象,循环,直到结束
                service.saveExcel(campus);
            }
            return "1";
        } catch (Exception e) {
            e.printStackTrace();
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            return "-1";
        }
    }


}
